package com.ship.dispatch.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ship.dispatch.bean.SpYearPlanSchemeDetailHistory;
import com.ship.dispatch.vo.SpYearPlanSchemeDetailHistoryVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface SpYearPlanSchemeDetailHistoryMapper extends BaseMapper<SpYearPlanSchemeDetailHistory> {
    /**
     * 尝试排布
     *
     * @return
     */
    @Select("select \n" +
            "f.*\n" +
            "from sp_year_plan_scheme_detail_history f\n" +
            "left JOIN sp_year_plan_scheme b on b.create_by = f.create_by and f.scheme_id = b.id\n" +
            "left JOIN sp_yearly_plan a on a.id = b.plan_id\n" +
            "where a.`year` = #{year} and f.create_by = #{createBy} and f.ship_name = #{shipName} and scheme_id = #{schemeId}")
    List<SpYearPlanSchemeDetailHistoryVO> getShipTryToArrange(@Param("year") Integer year, @Param("createBy") String createBy, @Param("shipName") String shipName, @Param("schemeId") Long schemeId);
    @Select("select \n" +
            "f.*\n" +
            "from sp_year_plan_scheme_detail_history f\n" +
            "left JOIN sp_year_plan_scheme b on b.create_by = f.create_by and f.scheme_id = b.id\n" +
            "left JOIN sp_yearly_plan a on a.id = b.plan_id\n" +
            "where a.`year` = #{year} and f.create_by = #{createBy} and f.goods_num = #{goodsNum} and scheme_id = #{schemeId}")
    List<SpYearPlanSchemeDetailHistoryVO> getGoodsNumTryToArrange(@Param("year") Integer year, @Param("createBy") String createBy, @Param("goodsNum") String goodsNum, @Param("schemeId") Long schemeId);
    @Select("select \n" +
            "f.*\n" +
            "from sp_year_plan_scheme_detail_history f\n" +
            "left JOIN sp_year_plan_scheme b on b.create_by = f.create_by and f.scheme_id = b.id\n" +
            "left JOIN sp_yearly_plan a on a.id = b.plan_id\n" +
            "where a.`year` = #{year} and f.create_by = #{createBy} and f.seller = #{seller} and scheme_id = #{schemeId}")
    List<SpYearPlanSchemeDetailHistoryVO> getSellerTryToArrange(@Param("year") Integer year, @Param("createBy") String createBy, @Param("seller") String seller, @Param("schemeId") Long schemeId);
    /**
     * 船舶名称集合
     *
     * @return
     */
    @Select("select \n" +
            "d.ship_name\n" +
            "from sp_year_plan_scheme s \n" +
            "LEFT JOIN sp_yearly_plan p on s.plan_id = p.id \n" +
            "Left JOIN sp_year_plan_scheme_detail_history d on d.plan_id = p.id and s.create_by = d.create_by and d.scheme_id = s.id\n" +
            "where d.create_by = #{createBy} \n" +
            "GROUP BY d.ship_name ")
    List<String> getPlanDetailsHistoryByShipName(@Param("createBy") String createBy);

    /**
     * 货源名称集合
     *
     * @return
     */
    @Select("select \n" +
            "d.goods_num\n" +
            "from sp_year_plan_scheme s \n" +
            "LEFT JOIN sp_yearly_plan p on s.plan_id = p.id \n" +
            "Left JOIN sp_year_plan_scheme_detail_history d on d.plan_id = p.id and s.create_by = d.create_by and d.scheme_id = s.id\n" +
            "where d.create_by = #{createBy} \n" +
            "GROUP BY d.goods_num")
    List<String> getPlanDetailsHistoryByGoodsName(@Param("createBy") String createBy);

    /**
     * 资源方集合
     *
     * @return
     */
    @Select("select \n" +
            "d.seller\n" +
            "from sp_year_plan_scheme s \n" +
            "LEFT JOIN sp_yearly_plan p on s.plan_id = p.id \n" +
            "Left JOIN sp_year_plan_scheme_detail_history d on d.plan_id = p.id and s.create_by = d.create_by and d.scheme_id = s.id\n" +
            "where d.create_by = #{createBy} \n" +
            "GROUP BY d.seller")
    List<String> getPlanDetailsHistoryBySellerName(@Param("createBy") String createBy);

    @Select("select h.* from sp_year_plan_scheme s\\n\" +\n" +
            "            \"LEFT JOIN sp_year_plan_scheme_detail_history h on h.scheme_id = s.id and s.plan_id = h.plan_id and s.create_by = h.create_by\\n\" +\n" +
            "            \"where  s.create_by = #{createBy} and s.is_share = 1 and h.scheme_id = #{id} and h.scheme_detail_id in (select scheme_detail_id from sp_year_plan_scheme_detail_history where id = #{schemeDetailId} and scheme_id= #{schemeId})")
    List<SpYearPlanSchemeDetailHistoryVO> getShareHistory(@Param("createBy") String createBy, @Param("schemeDetailId") Long schemeDetailId, @Param("schemeId") Long schemeId, @Param("id") Long id);

    @Select("select h.* from sp_year_plan_scheme s\n" +
            "LEFT JOIN sp_year_plan_scheme_detail_history h on h.scheme_id = s.id and s.plan_id = h.plan_id and s.create_by = h.create_by\n" +
            "where  s.create_by = #{createBy} and s.is_draft = 1 and h.scheme_id = #{id} and h.scheme_detail_id in (select scheme_detail_id from sp_year_plan_scheme_detail_history where id = #{schemeDetailId} and scheme_id= #{schemeId})")
    List<SpYearPlanSchemeDetailHistoryVO> getDraftHistory(@Param("createBy") String createBy, @Param("schemeDetailId") Long schemeDetailId, @Param("schemeId") Long schemeId, @Param("id") Long id);
}
